<?php
namespace Admin\Model;

use Common\Model\BaseModel;
use Common\Model\AlibabaModel;

/**
 * 发货并导出订单
 * 
 * @author lanxuebao
 *
 */
class ExportSendOrderModel extends BaseModel
{
    protected $tableName = 'mall_trade';
    
    public function sendAndExport($shopId, $uid){
        if (PHP_SAPI == 'cli')
            die('This example should only be run from a Web Browser');
        ignore_user_abort(true);
        set_time_limit(0);

        $parameters = $_GET;
        $where = array("trade.seller_id=".$shopId, "trade.`status`='tosend'");
        //下单时间
        if($parameters['start_date'] != '' && $parameters['end_date'] != ''){
            $where[] = "trade.created BETWEEN '".addslashes($parameters['start_date'])."' AND '".addslashes($parameters['end_date'])."'";
        }else{
            $this->error = '必须选择下单时间';
            return false;
        }

        if(is_numeric($parameters['tid']))
            $where[] = 'trade.tid='.$parameters['tid'];
        if($parameters['buyer_nick'])
            $where[] = "trade.buyer_nick='".addslashes($parameters['buyer_nick'])."'";
        
        //付款时间
        if($parameters['pay_start_date'] != '' && $parameters['pay_end_date'] != ''){
            $where[] = "trade.pay_time BETWEEN '".addslashes($parameters['pay_start_date'])."' AND '".addslashes($parameters['pay_end_date'])."'";
        }else if($parameters['pay_start_date'] != ''){
            $where[] = "trade.pay_time>='".addslashes($parameters['pay_start_date'])."'";
        }else if($parameters['pay_end_date'] != ''){
            $where[] = "trade.pay_time<='".addslashes($parameters['pay_end_date'])."'";
        }
        
        if($parameters['receiver_name'])
            $where[] = "trade.receiver_name='".addslashes($parameters['receiver_name'])."'";
        if(is_numeric($parameters['receiver_mobile']) && strlen($parameters['receiver_mobile']) >= 11)
            $where[] = "trade.receiver_mobile='".addslashes($parameters['receiver_mobile'])."'";
        
        $where = implode(' AND ', $where);
        // 限制一次导出超过1000条就截至
        $last_id = $list = $this->alias("trade")->field("trade.tid")
            ->join("mall_order AS `order` ON `order`.tid=trade.tid")
            ->where($where)
            ->order("`trade`.tid DESC")
            ->limit(999,1)
            ->find();
        if($last_id){
            $where .= " AND trade.tid<=".$last_id['tid'];
        }
        
        // 读取订单
        $selectSQL = "SELECT trade.tid,trade.type, trade.kind, trade.total_num,trade.`status`, trade.paid_fee, trade.buyer_id, trade.express_id, trade.refund_state AS trade_refund_state,
                         trade.receiver_name, trade.receiver_mobile, trade.receiver_phone, trade.receiver_province, trade.buyer_remark, trade.seller_id,
                         trade.receiver_city, trade.receiver_county, trade.receiver_detail, trade.receiver_zip, trade.total_weight, 
                         trade.post_fee, trade.discount_fee AS trade_discount_fee, trade.payment AS trade_payment,
                        `order`.title, `order`.num, `order`.price, `order`.outer_id, `order`.sku_json,`order`.product_id, `order`.weight,
                        `order`.payment, `order`.discount_fee, `order`.total_fee, `order`.goods_id,
                         refund.refund_state, refund.refund_num
                     FROM mall_trade AS trade
                     INNER JOIN mall_order AS `order` ON `order`.tid=trade.tid
                     LEFT JOIN mall_trade_refund AS refund ON refund.refund_id=`order`.oid
                     WHERE {$where}
                     ORDER BY trade.tid";
        
        $list = $this->query($selectSQL);
        if(empty($list)){
            $this->error = '无待发货订单';
            return false;
        }
        
        //查找导出模板
        $_template = $this->query("SELECT * FROM shop_template WHERE shop_id='{$shopId}' AND type=1 LIMIT 1");
        if(empty($_template)){
            $this->error = '未上传待发货excel模板';
            return false;
        }
        $_template = $_template[0];
        
        $StaticModel = D('Static');
        $expressList = $StaticModel->express(false, 'id');
        
        // 开始数据处理
        $tradeList = $productSpec = $alibabaList = $goodsList = array();
        foreach($list as $i=>$item){
            if($item['trade_refund_state'] == 'partial_refunding'  || $item['trade_refund_state'] == 'full_refunding' ){
                continue;
            }
            
            $item['discount_price'] = sprintf('%.2f', $item['payment'] / $item['num']);
            
            $item['refund_num'] = 0;
            if($item['refund_num'] > 0 && ($item['refund_state'] == 2 || $item['refund_state'] == 2.1 || $item['refund_state'] == 3)){
                if($item['refund_num'] == $item['num']){ // 全都退了
                    continue;
                }else{
                    $item['num'] -= $item['refund_num'];
                }
            }
        
            // 主订单
            if(!isset($tradeList[$item['tid']])){
                $tradeList[$item['tid']] = array(
                    'tid'                 => $item['tid'],
                    'created'             => $item['created'],
                    'receiver_name'       => $item['receiver_name'],
                    'receiver_mobile'     => $item['receiver_mobile'],
                    'receiver_province'   => $item['receiver_province'],
                    'receiver_city'       => $item['receiver_city'],
                    'receiver_county'     => $item['receiver_county'],
                    'receiver_detail'     => $item['receiver_detail'],
                    'receiver_zip'        => $item['receiver_zip'] > 0 ? $item['receiver_zip'] : '',
                    'seller_nick'         => $item['seller_nick'],
                    'seller_remark'       => $item['seller_remark'],
                    'buyer_nick'          => $item['buyer_nick'],
                    'post_fee'            => $item['post_fee'],
                    'discount_fee'        => $item['trade_discount_fee'],
                    'payment'             => $item['trade_payment'],
                    'pay_time'            => $item['pay_time'],
                    'refund_state'        => $item['refund_state'],
                    'refunded_fee'        => $item['refunded_fee'],
                    'express_id'          => $item['express_id'],
                    'express_name'        => $expressList[$item['express_id']]['name'],
                    'kind'                => 0,
                    'total_num'           => 0,
                    'total_weight'        => 0,
                    'body'                => '',
                    'paid_fee'            => $item['paid_fee'],
                    'refunded_fee'        => $item['refunded_fee'],
                    'trade_refund_state'  => $item['trade_refund_state'],
                    'buyer_remark'        => $item['buyer_remark'],
                    'orders'              => array()
                );
                
                if($item['type'] == '1688'){
                    $alibabaList[] = $item['tid'];
                }
            }

            $tradeList[$item['tid']]['kind']++;
            $tradeList[$item['tid']]['total_num'] += $item['num'];
            $tradeList[$item['tid']]['total_weight'] += $item['num'] * $item['weight'];
            
            if(!isset($productSpec[$item['product_id']])){
                $spec = get_spec_name($item['sku_json']);
                $productSpec[$item['product_id']] = $spec;
            }else{
                $spec =  $productSpec[$item['product_id']];
            }
            
            $tradeList[$item['tid']]['body'] .= (count($tradeList[$item['tid']]['orders']) == 0 ? "" : "\r\n").$item['title'].$spec."(".$item['num']."件)";
            
            // 子订单
            $tradeList[$item['tid']]['orders'][] = array(
                'oid'           => $item['oid'],
                'outer_id'      => $item['outer_id'],
                'title'         => $item['title'],
                'spec'          => $spec,
                'price'         => $item['price'],
                'num'           => $item['num'],
                'refund_num'    => $item['refund_num'],
                'payment'       => $item['payment'],
                'discount_price'=> $item['discount_price']
            );
            
            // 产品信息合并
            if(!isset($goodsList[$item['product_id']])){
                $goodsList[$item['product_id']] = array(
                    'goods_id'       => $item['goods_id'],
                    'outer_id'       => $item['outer_id'],
                    'title'          => $item['title'],
                    'spec'           => $item['spec'],
                    'num'            => $item['num'],
                    'refund_num'     => $item['refund_num'],
                    'total_fee'      => $item['total_fee'],
                    'payment'        => $item['payment']
                );
            }else{
                $goodsList[$item['product_id']]['num']          += $item['num'];
                $goodsList[$item['product_id']]['refund_num']   += $item['refund_num'];
                $goodsList[$item['product_id']]['total_fee']    += $item['total_fee'];
                $goodsList[$item['product_id']]['payment']      += $item['payment'];
            }
        }
        unset($productSpec);

        // 加载PHPExcel
        Vendor('PHPExcel.PHPExcel.IOFactory');
        $objPHPExcel = null;
        $filename = $_SERVER['DOCUMENT_ROOT'].'/../Upload/Template/ToOut/'.$_template['filename'];
        if(substr($_template['field'], -4) == '.php'){   // 使用php模板
            $objPHPExcel = \PHPExcel_IOFactory::load($filename);
            $setCellValue = require_once MODULE_PATH.'Model/ExportOrder/'.$_template['field'];
            $setCellValue($objPHPExcel, $tradeList);
        }else{ // excel列对应数据库字段
            $templates = json_decode($_template['field'], true);
            $objPHPExcel = \PHPExcel_IOFactory::load($filename);
            $this->setCellValue($objPHPExcel, $tradeList, $templates);
        }
        
        // 导出商品信息
        $this->exportGoodsInfo($objPHPExcel, $goodsList);
        unset($goodsList);
        
        // 设置文档基本属性
        $objPHPExcel->getProperties()
                    ->setTitle(date('Y-m-d H:i:s'))
                    ->setSubject($parameters['start_date'].'~'.$parameters['end_date'])
                    ->setDescription(json_encode($parameters, JSON_UNESCAPED_UNICODE));
        
        //把文件下载到服务器
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $month = date('Y-m');
        $folder = '../Upload/exportSend/'.$month;
        if(!@is_dir($folder)){
            mkdir ($folder, 0777, true);
        }
        $savename = date("Y-m-d His").'_'.$shopId.'.xlsx';
        $objWriter->save($folder.'/'.$savename);
        
        // 保存数据修改
        $tradeCount = count($tradeList);
        for($i=0; $i<$tradeCount; $i+=100){
            $keys = array_slice($tradeList, $i, 100);
            $keys = array_keys($keys);
            $this->execute("UPDATE mall_trade SET `status`='toout' WHERE tid IN (".implode(',', $keys).")");
        }
        unset($tradeList);
        
        $sql = "INSERT INTO shop_export
                SET
                    shop_id='{$shopId}',
                    uid='{$uid}',
                    created='".date('Y-m-d H:i:s')."',
                    filename='{$month}/{$savename}',
                    trade_count={$tradeCount},
                    `sql`='".addslashes($selectSQL)."',
                    parameters='".json_encode($parameters, JSON_UNESCAPED_UNICODE)."'";
        
        $this->execute($sql);
        
        // Redirect output to a client’s web browser (Excel2007)
        $text = iconv('UTF-8', 'GB2312', '订单');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$text.date('YmdHis').'.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        
        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        
        $objWriter->save('php://output');
        
        header('X-Accel-Buffering: no');
        header('Content-Length: '. strlen(ob_get_contents()));
        header("Connection: close");
        header("HTTP/1.1 200 OK");
        ob_end_flush();
        flush();
        
        // 1688订单批量下单
        if(count($alibabaList) > 0){
            $aliModel = new AlibabaModel();
            $aliModel->commitOrder($alibabaList);
        }
        exit;
    }
    
    /**
    * 单元格赋值
    */
    private function setCellValue(\PHPExcel $objPHPExcel, $tradeList, $templates){
        foreach($tradeList as $tid=>$trade){
            $trade['total_weight'] = sprintf('%.2f', $trade['total_weight']);
            $_express_id = isset($templates[$trade['express_id']]) ? $trade['express_id'] : 0;
            
            $template = $templates[$_express_id];
            $worksheet = $objPHPExcel->getSheet($template['sheet']);
            $i = $template['start'];
            $templates[$_express_id]['start']++;
            
            // 单元格赋值
            foreach($template['field'] as $column=>$field){
                $worksheet->setCellValue($column.$i, $trade[$field]);
            }
        }
    }
    
    /**
     * 导出商品信息
     */
    private function exportGoodsInfo(\PHPExcel $objPHPExcel, array $goodsList){
        $sheetCount = $objPHPExcel->getSheetCount();
        $workSheet = $objPHPExcel->createSheet($sheetCount);
        $workSheet->setTitle('商品信息');
        $workSheet->setCellValue('A1', '产品编码');
        $workSheet->setCellValue('B1', '产品名称');
        $workSheet->setCellValue('C1', '产品规格');
        $workSheet->setCellValue('D1', '总数量');
        $workSheet->setCellValue('E1', '总金额');
        $workSheet->setCellValue('F1', '总实付');
        $workSheet->setCellValue('G1', '总退款数');
        $workSheet->setCellValue('H1', '商品ID');
        
        $index = 1;
        foreach ($goodsList as $item){
            $index++;
            $workSheet->setCellValue('A'.$index, $item['outer_id']);
            $workSheet->setCellValue('B'.$index, $item['title']);
            $workSheet->setCellValue('C'.$index, $item['spec']);
            $workSheet->setCellValue('D'.$index, $item['num'] + $item['refund_num']);
            $workSheet->setCellValue('E'.$index, sprintf('%.2f', $item['total_fee']));
            $workSheet->setCellValue('F'.$index, sprintf('%.2f', $item['payment']));
            $workSheet->setCellValue('G'.$index, $item['refund_num']);
            $workSheet->setCellValue('H'.$index, $item['goods_id']);
        }
    }
    
    /**
     * 导出产品销量
     * @param unknown $worksheet1
     * @param unknown $i
     * @param unknown $outerProduct
     * @param unknown $remarkProduct
     */
    public function productSale($worksheet, $i, $outerProduct, $remarkProduct){
        if(!empty($outerProduct)){
            foreach($outerProduct as $k=>$v){
                $i++;
                $worksheet
                ->setCellValueExplicit('A'.$i, $k, \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('B'.$i, $v['titel'])
                ->setCellValue('C'.$i, $v['spec'])
                ->setCellValue('D'.$i, $v['num'])
                ->setCellValue('E'.$i, $v['total_price'])
                ->setCellValueExplicit('F'.$i, $v['product_id'], \PHPExcel_Cell_DataType::TYPE_STRING);
            }
        }
        
        if(!empty($remarkProduct)){
            foreach($remarkProduct as $k=>$v){
                $i++;
                $worksheet
                ->setCellValueExplicit('A'.$i, $v['outer_id'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('B'.$i, $v['titel'])
                ->setCellValue('C'.$i, $v['spec'])
                ->setCellValue('D'.$i, $v['num'])
                ->setCellValue('E'.$i, $v['total_price'])
                ->setCellValueExplicit('F'.$i, $v['product_id'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('G'.$i, $v['buyer_remark']);
            }
        }
    }
    
    /**
     * 导出相同产品不同价格的销量
     * @param unknown $worksheet4
     * @param unknown $iiii
     * @param unknown $samePrice
     */
    private function samePrice($worksheet, $i, $samePrice){
        if(!empty($samePrice)){
            foreach($samePrice as $k=>$v){
                $i++;
                $worksheet
                ->setCellValueExplicit('A'.$i, $v['outer_id'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('B'.$i, $v['title'])
                ->setCellValue('C'.$i, $v['spec'])
                ->setCellValue('D'.$i, $v['num'])
                ->setCellValue('E'.$i, $v['price']);
            }
        }
    }
    
    /**
     * 导出订单
     */
    public function printOrder($shopId){
        if (PHP_SAPI == 'cli')
            die('This example should only be run from a Web Browser');
        set_time_limit(0);
        
        $parameters = $_GET;
        
        $where = array();
        if($parameters['tid'])
            $where['trade.tid'] = $parameters['tid'];
        if($parameters['buyer_nick'])
            $where['trade.buyer_nick'] = $parameters['buyer_nick'];
        //下单时间
        if($parameters['start_date'] != '')
            $where['trade.created'] = array('egt', $parameters['start_date']);
        if($parameters['end_date'] != '')
            $where['trade.created'] = array('elt', $parameters['end_date']);
        if($parameters['start_date'] != '' && $parameters['end_date'] != '')
            $where['trade.created'] = array(array('egt', $parameters['start_date']),array('elt', $parameters['end_date']),'and');
        //付款时间
        if($parameters['pay_start_date'] != '')
            $where['trade.pay_time'] = array('egt', $parameters['pay_start_date']);
        if($parameters['pay_end_date'] != '')
            $where['trade.pay_time'] = array('elt', $parameters['pay_end_date']);
        if($parameters['pay_start_date'] != '' && $parameters['pay_end_date'] != '')
            $where['trade.pay_time'] = array(array('egt', $parameters['pay_start_date']),array('elt', $parameters['pay_end_date']),'and');
        
        if($parameters['receiver_name'])
            $where['trade.receiver_name'] = $parameters['receiver_name'];
        if($parameters['receiver_mobile'])
            $where['trade.receiver_mobile'] = $parameters['receiver_mobile'];
        if($parameters['status'])
            $where['trade.`status`'] = $parameters['status'];
        $where['trade.seller_id']  = $shopId;
        
        // 读取订单
        $list = $this->alias("trade")
                     ->field("trade.tid, trade.pay_type,trade.kind, trade.total_fee, trade.post_fee, trade.payment, trade.paid_balance, trade.total_num,trade.`status`, trade.paid_fee, trade.buyer_id, trade.express_id,trade.paid_no_balance,
                              trade.receiver_name, trade.receiver_mobile, trade.receiver_phone, trade.receiver_province, trade.buyer_remark, trade.merge_pay,
                              trade.receiver_city, trade.receiver_county, trade.receiver_detail, trade.receiver_zip, trade.created, trade.pay_time, 
                             `order`.title, `order`.outer_id, `order`.num, `order`.price, `order`.sku_json,order.product_id")
                     ->join("mall_order AS `order` ON `order`.tid=trade.tid")
                     ->where($where)
                     ->order("`trade`.tid")
                     ->select();

        if(empty($list)){
            $this->error = '暂无订单';
            return false;
        }
        
        // 加载PHPExcel
        vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
        
        // 设置文档基本属性
        $objPHPExcel->getProperties()
        ->setCreator("微通联盟")
        ->setLastModifiedBy("微通联盟")
        ->setTitle(date('Y-m-d H:i:s'))
        ->setSubject($parameters['start_date'].'~'.$parameters['end_date'])
        ->setDescription(json_encode($_POST));
        
        // 读取工作表
        $worksheet = $objPHPExcel->setActiveSheetIndex(0);
        $worksheet->setTitle('订单列表');
        
        $i=1;
        $worksheet
        ->setCellValue('A'.$i, '订单号')
        ->setCellValue('B'.$i, '产品种类')
        ->setCellValue('C'.$i, '订单状态')
        ->setCellValue('D'.$i, '产品编码')
        ->setCellValue('E'.$i, '产品名称')
        ->setCellValue('F'.$i, '产品规格')
        ->setCellValue('G'.$i, '单品售价')
        ->setCellValue('H'.$i, '数量')
        ->setCellValue('I'.$i, '订单总价')
        ->setCellValue('J'.$i, '邮费')
        ->setCellValue('K'.$i, '直接支付金额')
        ->setCellValue('L'.$i, '买家姓名')
        ->setCellValue('M'.$i, '买家收货省')
        ->setCellValue('N'.$i, '买家收货市')
        ->setCellValue('O'.$i, '买家收货区')
        ->setCellValue('P'.$i, '买家收货地址')
        ->setCellValue('Q'.$i, '买家手机号码')
        ->setCellValue('R'.$i, '买家邮编')
        ->setCellValue('S'.$i, '买家留言')
        ->setCellValue('T'.$i, '下单日期')
        ->setCellValue('U'.$i, '付款时间')
        ->setCellValue('V'.$i, '支付单号');
        
        $trade = array();
        $productSpec = array();
        $spec = "";
        //订单状态
        $StaticModel = D('Static');
        $orderStatus = $StaticModel->orderStatus();
        $orderModel = D('Order');
        
        foreach($list as $item){
            $i++;
            $spec = '';
            if(!isset($productSpec[$item['product_id']])){
                $spec = get_spec_name($item['sku_json']);
                $productSpec[$item['product_id']] = $spec;
            }else{
                $spec =  $productSpec[$item['product_id']];
            }
            if(!isset($trade[$item['tid']])){
                //判定订单是否超时
                $orderModel->timeout($item);
                
                $worksheet
                ->setCellValueExplicit('A'.$i, $item['tid'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('B'.$i, $item['kind'])
                ->setCellValue('C'.$i, $orderStatus[$item['status']])
                ->setCellValueExplicit('D'.$i, $item['outer_id'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('E'.$i, $item['title'])
                ->setCellValue('F'.$i, $spec)
                ->setCellValueExplicit('G'.$i, $item['price'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit('H'.$i, $item['num'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit('I'.$i, $item['total_fee'] + $item['post_fee'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit('J'.$i, $item['post_fee'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit('K'.$i, $item['payment'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('L'.$i, $item['receiver_name'])
                ->setCellValue('M'.$i, $item['receiver_province'])
                ->setCellValue('N'.$i, $item['receiver_city'])
                ->setCellValue('O'.$i, $item['receiver_county'])
                ->setCellValue('P'.$i, $item['receiver_detail'])
                ->setCellValueExplicit('Q'.$i, $item['receiver_mobile'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('R'.$i, $item['receiver_zip'])
                ->setCellValue('S'.$i, $item['buyer_remark'])
                ->setCellValue('T'.$i, $item['created'])
                ->setCellValue('U'.$i, $item['pay_time'])
                ->setCellValue('V'.$i, $item['merge_pay']);
                
                $worksheet->getStyle('A'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('B'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('C'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('D'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('E'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('F'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('G'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('H'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('I'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('J'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('K'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('L'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('M'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('N'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('O'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('P'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('Q'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('R'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('S'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('T'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('U'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                $worksheet->getStyle('V'.$i)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);
                
                $trade[$item['tid']] = 1;
            }else{
                $trade[$item['tid']] ++;
                $worksheet
                ->setCellValueExplicit('D'.$i, $item['outer_id'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValue('E'.$i, $item['title'])
                ->setCellValue('F'.$i, $spec)
                ->setCellValueExplicit('G'.$i, $item['price'], \PHPExcel_Cell_DataType::TYPE_STRING)
                ->setCellValueExplicit('H'.$i, $item['num'], \PHPExcel_Cell_DataType::TYPE_STRING);
                
                if($item['kind'] == $trade[$item['tid']]){
                    $worksheet
                    ->mergeCells("A".($i-($trade[$item['tid']]-1)).":A".$i)
                    ->mergeCells("B".($i-($trade[$item['tid']]-1)).":B".$i)
                    ->mergeCells("C".($i-($trade[$item['tid']]-1)).":C".$i)
                    ->mergeCells("I".($i-($trade[$item['tid']]-1)).":I".$i)
                    ->mergeCells("J".($i-($trade[$item['tid']]-1)).":J".$i)
                    ->mergeCells("K".($i-($trade[$item['tid']]-1)).":K".$i)
                    ->mergeCells("L".($i-($trade[$item['tid']]-1)).":L".$i)
                    ->mergeCells("M".($i-($trade[$item['tid']]-1)).":M".$i)
                    ->mergeCells("N".($i-($trade[$item['tid']]-1)).":N".$i)
                    ->mergeCells("O".($i-($trade[$item['tid']]-1)).":O".$i)
                    ->mergeCells("P".($i-($trade[$item['tid']]-1)).":P".$i)
                    ->mergeCells("Q".($i-($trade[$item['tid']]-1)).":Q".$i)
                    ->mergeCells("R".($i-($trade[$item['tid']]-1)).":R".$i)
                    ->mergeCells("S".($i-($trade[$item['tid']]-1)).":S".$i)
                    ->mergeCells("T".($i-($trade[$item['tid']]-1)).":T".$i)
                    ->mergeCells("U".($i-($trade[$item['tid']]-1)).":U".$i)
                    ->mergeCells("V".($i-($trade[$item['tid']]-1)).":V".$i);
                }
            }
        }
        
        // Redirect output to a client’s web browser (Excel2007)
        $text = iconv('UTF-8', 'GB2312', '订单');
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$text.date('YmdHis').'.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        
        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }
}
?>